1 using System;
2 using System.Collections.Generic;
3 using System.ComponentModel;
4 using System.Data;
5 using System.Drawing;
6 using System.Linq;
7 using System.Text;
8 using System.Windows.Forms;
9 using System.Data.SqlClient;
10 using Excel = Microsoft.Office.Interop.Excel;
11 namespace WarehouseManagementSystem
12 {
13 public partial class frmCustomerOrders : Form
14 {
15
16
17 SqlConnection con = null;
18 SqlCommand cmd = null;
19 DataTable dt= new DataTable();
20 ConnectionString cs = new ConnectionString();
21
22 public frmCustomerOrders()
23 {
24 InitializeComponent();
25 }
26
27
28 private void Button7_Click(object sender, EventArgs e)
29 {
30 if (DataGridView3.DataSource == null)
31 {
32 MessageBox.Show("Sorry nothing to export into excel sheet..", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
33 return;
34 }
35 int rowsTotal = 0;
36 int colsTotal = 0;
37 int I = 0;
38 int j = 0;
39 int iC = 0;
40 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
41 Excel.Application xlApp = new Excel.Application();
42
43 try
44 {
45 Excel.Workbook excelBook = xlApp.Workbooks.Add();
46 Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelBook.Worksheets[1];
47 xlApp.Visible = true;
48
49 rowsTotal = DataGridView3.RowCount;
50 colsTotal = DataGridView3.Columns.Count - 1;
51 var _with1 = excelWorksheet;
52 _with1.Cells.Select();
53 _with1.Cells.Delete();
54 for (iC = 0; iC <= colsTotal; iC++)
55 {
56 _with1.Cells[1, iC + 1].Value = DataGridView3.Columns[iC].HeaderText;
57 }
58 for (I = 0; I <= rowsTotal - 1; I++)
59 {
60 for (j = 0; j <= colsTotal; j++)
61 {
62 _with1.Cells[I + 2, j + 1].value = DataGridView3.Rows[I].Cells[j].Value;
63 }
64 }
65 _with1.Rows["1:1"].Font.FontStyle = "Bold";
66 _with1.Rows["1:1"].Font.Size = 12;
67
68 _with1.Cells.Columns.AutoFit();
69 _with1.Cells.Select();
70 _with1.Cells.EntireColumn.AutoFit();
71 _with1.Cells[1, 1].Select();
72 }
73 catch (Exception ex)
74 {
75 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
76 }
77 finally
78 {
79 //RELEASE ALLOACTED RESOURCES
80 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
81 xlApp = null;
82 }
83 }
84
85 private void Button9_Click(object sender, EventArgs e)
86 {
87 DataGridView3.DataSource = null;
88 GroupBox4.Visible = false;
89 }
90
91
92
93 private void Button1_Click(object sender, EventArgs e)
94 {
95 try
96 {
97 GroupBox3.Visible = true;
98 con = new SqlConnection(cs.DBConn);
99 con.Open();
100 cmd = new SqlCommand("SELECT RTRIM(invoiceNo) as [Order No],RTRIM(InvoiceDate) as [Order Date],RTRIM(Invoice_Info.CustomerID) as [Customer ID],RTRIM(CustomerName) as [Customer Name],RTRIM(SubTotal) as [SubTotal],RTRIM(VATPer) as [Vat+ST %],RTRIM(VATAmount) as [VAT+ST Amount],RTRIM(DiscountPer) as [Discount %],RTRIM(DiscountAmount) as [Discount Amount],RTRIM(GrandTotal) as [Grand Total],RTRIM(TotalPayment) as [Total Payment],RTRIM(PaymentDue) as [Payment Due],RTRIM(PaymentType) as [Payment Type],RTRIM(Status) as [Status],Remarks from Invoice_Info,Customer where Invoice_Info.CustomerID=Customer.CustomerID and InvoiceDate between @d1 and @d2 and Customer.CustomerID='" + txtCustomerID.Text + "' order by InvoiceDate desc", con);
101 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "InvoiceDate").Value = dtpInvoiceDateFrom.Value.Date;
102 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "InvoiceDate").Value = dtpInvoiceDateTo.Value.Date;
103 SqlDataAdapter myDA = new SqlDataAdapter(cmd);
104 DataSet myDataSet = new DataSet();
105 myDA.Fill(myDataSet, "Invoice_Info");
106 myDA.Fill(myDataSet, "Customer");
107 DataGridView1.DataSource = myDataSet.Tables["Customer"].DefaultView;
108 DataGridView1.DataSource = myDataSet.Tables["Invoice_Info"].DefaultView;
109 Int64 sum = 0;
110 Int64 sum1 = 0;
111 Int64 sum2 = 0;
112
113 foreach (DataGridViewRow r in this.DataGridView1.Rows)
114 {
115 Int64 i = Convert.ToInt64(r.Cells[9].Value);
116 Int64 j = Convert.ToInt64(r.Cells[10].Value);
117 Int64 k = Convert.ToInt64(r.Cells[11].Value);
118 sum = sum + i;
119 sum1 = sum1 + j;
120 sum2 = sum2 + k;
121
122 }
123 TextBox1.Text = sum.ToString();
124 TextBox2.Text = sum1.ToString();
125 TextBox3.Text = sum2.ToString();
126
127 con.Close();
128 }
129 catch (Exception ex)
130 {
131 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
132 }
133 }
134
135
136 private void DataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
137 {
138 string strRowNumber = (e.RowIndex + 1).ToString();
139 SizeF size = e.Graphics.MeasureString(strRowNumber, this.Font);
140 if (DataGridView1.RowHeadersWidth < Convert.ToInt32((size.Width + 20)))
141 {
142 DataGridView1.RowHeadersWidth = Convert.ToInt32((size.Width + 20));
143 }
144 Brush b = SystemBrushes.ControlText;
145 e.Graphics.DrawString(strRowNumber, this.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2));
146
147 }
148
149 private void DataGridView3_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
150 {
151 string strRowNumber = (e.RowIndex + 1).ToString();
152 SizeF size = e.Graphics.MeasureString(strRowNumber, this.Font);
153 if (DataGridView3.RowHeadersWidth < Convert.ToInt32((size.Width + 20)))
154 {
155 DataGridView3.RowHeadersWidth = Convert.ToInt32((size.Width + 20));
156 }
157 Brush b = SystemBrushes.ControlText;
158 e.Graphics.DrawString(strRowNumber, this.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2));
159
160 }
161
162
163 private void TabControl1_Click(object sender, EventArgs e)
164 {
165 DataGridView1.DataSource = null;
166 dtpInvoiceDateFrom.Text = DateTime.Today.ToString();
167 dtpInvoiceDateTo.Text = DateTime.Today.ToString();
168 GroupBox3.Visible = false;
169 DataGridView3.DataSource = null;
170 GroupBox4.Visible = false;
171
172 }
173
174
175
176 private void cmbCustomerName_Format(object sender, ListControlConvertEventArgs e)
177 {
178 if (object.ReferenceEquals(e.DesiredType, typeof(string)))
179 {
180 e.Value = e.Value.ToString().Trim();
181 }
182 }
183
184 private void button4_Click(object sender, EventArgs e)
185 {
186 try
187 {
188 GroupBox4.Visible = true;
189 con = new SqlConnection(cs.DBConn);
190 con.Open();
191 cmd = new SqlCommand("SELECT RTRIM(invoiceNo) as [Order No],RTRIM(InvoiceDate) as [Order Date],RTRIM(Invoice_Info.CustomerID) as [Customer ID],RTRIM(CustomerName) as [Customer Name],RTRIM(SubTotal) as [SubTotal],RTRIM(VATPer) as [Vat+ST %],RTRIM(VATAmount) as [VAT+ST Amount],RTRIM(DiscountPer) as [Discount %],RTRIM(DiscountAmount) as [Discount Amount],RTRIM(GrandTotal) as [Grand Total],RTRIM(TotalPayment) as [Total Payment],RTRIM(PaymentDue) as [Payment Due],RTRIM(PaymentType) as [Payment Type],RTRIM(Status) as [Status],Remarks from Invoice_Info,Customer where Invoice_Info.CustomerID=Customer.CustomerID and Customer.CustomerID='" + txtCustomerID.Text + "' order by CustomerName,InvoiceDate", con);
192 SqlDataAdapter myDA = new SqlDataAdapter(cmd);
193 DataSet myDataSet = new DataSet();
194 myDA.Fill(myDataSet, "Invoice_Info");
195 myDA.Fill(myDataSet, "Customer");
196 DataGridView3.DataSource = myDataSet.Tables["Customer"].DefaultView;
197 DataGridView3.DataSource = myDataSet.Tables["Invoice_Info"].DefaultView;
198 Int64 sum = 0;
199 Int64 sum1 = 0;
200 Int64 sum2 = 0;
201
202 foreach (DataGridViewRow r in this.DataGridView3.Rows)
203 {
204 Int64 i = Convert.ToInt64(r.Cells[9].Value);
205 Int64 j = Convert.ToInt64(r.Cells[10].Value);
206 Int64 k = Convert.ToInt64(r.Cells[11].Value);
207 sum = sum + i;
208 sum1 = sum1 + j;
209 sum2 = sum2 + k;
210 }
211 TextBox6.Text = sum.ToString();
212 TextBox5.Text = sum1.ToString();
213 TextBox4.Text = sum2.ToString();
214
215 con.Close();
216 }
217 catch (Exception ex)
218 {
219 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
220 }
221 }
222
223 private void Button3_Click(object sender, EventArgs e)
224 {
225 if (DataGridView1.DataSource == null)
226 {
227 MessageBox.Show("Sorry nothing to export into excel sheet..", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
228 return;
229 }
230 int rowsTotal = 0;
231 int colsTotal = 0;
232 int I = 0;
233 int j = 0;
234 int iC = 0;
235 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
236 Excel.Application xlApp = new Excel.Application();
237
238 try
239 {
240 Excel.Workbook excelBook = xlApp.Workbooks.Add();
241 Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelBook.Worksheets[1];
242 xlApp.Visible = true;
243
244 rowsTotal = DataGridView1.RowCount;
245 colsTotal = DataGridView1.Columns.Count - 1;
246 var _with1 = excelWorksheet;
247 _with1.Cells.Select();
248 _with1.Cells.Delete();
249 for (iC = 0; iC <= colsTotal; iC++)
250 {
251 _with1.Cells[1, iC + 1].Value = DataGridView1.Columns[iC].HeaderText;
252 }
253 for (I = 0; I <= rowsTotal - 1; I++)
254 {
255 for (j = 0; j <= colsTotal; j++)
256 {
257 _with1.Cells[I + 2, j + 1].value = DataGridView1.Rows[I].Cells[j].Value;
258 }
259 }
260 _with1.Rows["1:1"].Font.FontStyle = "Bold";
261 _with1.Rows["1:1"].Font.Size = 12;
262
263 _with1.Cells.Columns.AutoFit();
264 _with1.Cells.Select();
265 _with1.Cells.EntireColumn.AutoFit();
266 _with1.Cells[1, 1].Select();
267 }
268 catch (Exception ex)
269 {
270 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
271 }
272 finally
273 {
274 //RELEASE ALLOACTED RESOURCES
275 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
276 xlApp = null;
277 }
278 }
279
280 private void Button2_Click(object sender, EventArgs e)
281 {
282 DataGridView1.DataSource = null;
283 dtpInvoiceDateFrom.Text = DateTime.Today.ToString();
284 dtpInvoiceDateTo.Text = DateTime.Today.ToString();
285 GroupBox3.Visible = false;
286 }
287
288 }
289
290 }
